-- creator   xiongv
-- date      2023-05-29
-- 功能       虚假揽收汇总
-- 依赖表     jms_dm__dm_waybill_false_taking_dt

drop table if exists jms_dm.dm_waybill_false_taking_summary_dt;
create external table if not exists jms_dm.dm_waybill_false_taking_summary_dt(
     taking_date                           string     comment '揽收日期'
    ,receive_network_code                 string     comment '收件网点编码'
    ,receive_network_name                 string     comment '收件网点名称'
    ,receive_fran_code                    string     comment '收件加盟商编码'
    ,receive_fran_name                    string     comment '收件加盟商名称'
    ,receive_agent_code                   string     comment '收件代理区编码'
    ,receive_agent_name                   string     comment '收件代理区名称'
    ,receive_virt_agent_code              string     comment '收件虚拟代理区编码'
    ,receive_virt_agent_name              string     comment '收件虚拟代理区名称'
    ,order_source_code                    string     comment '订单来源'
    ,taking_user_code                     string     comment '揽收业务员'
    ,taking_user_name                     string     comment '揽收业务员'
    ,customer_code                        string     comment '客户'
    ,customer_name                        string     comment '客户'
    ,receive_network_taking_cnt           bigint     comment '网点揽收数量'
    ,receive_network_send_cnt             bigint     comment '网点发件数量'
    ,collect_arr_cnt                      bigint     comment '集散到件数量'
    ,center_arr_cnt                       bigint     comment '中心到件数量'
    ,receive_network_false_taking_cnt     bigint     comment '网点全部虚假揽收票数'
    ,receive_network_should_hand_in_cnt   bigint     comment '网点全部应交件票数'
    ,interrupted_taking_cnt               bigint     comment '截单后揽收数'
    ,false_taking_no_send_cnt             bigint     comment '虚假揽收未发件数'
    ,false_taking_no_hand_in_cnt          bigint     comment '虚假揽收未到件数'
    ,taking_duration_hour                 decimal(10,2) comment '揽收时长(只看虚假揽收)'
    ,all_arr_cnt                          bigint     comment '首集散+中心到件数量'
) comment '经营指标-虚假揽收汇总'
partitioned by (dt string comment '分区日期')
stored as parquet
location '/dw/hive/jms_dm.db/external/dm_waybill_false_taking_summary_dt'
tblproperties (
    'discover.partitions'='false',
    'parquet.column.index.access'='true'
);
